$webwork.htmlEncode($page.space.name) : Loading TIGER high-level-polygon county

This page last changed on Jul 03, 2006 by cholmes.

Not quite what you think

The county polygons that are formed may not be quite what you think. Remember, these are political boundaries, not land boundaries. This has many implications, but the biggest is that islands tend to be "connected" to mainland.

This builds a polygon for each county ("module") for the TIGER data. There are 3232 counties (one for each TIGER module).

There are quite a few issues with building up the county polygons, mostly due to TIGER data quality.

The TIGER data is laid out so that you should be able to look for edges (completechain) where the countyl/statel is different from countyr/stater. Unfortunately, the data is not always correct - there are sometime holes in the data between counties.

We use a slightly modified method that is summaried as follows.

For each county
   Load the appropriate edges 
           SELECT ... FROM ... 
              WHERE (statel=state and countyl=county) OR (stater=state and countyr=county ) 
   Build a polygon
   Save the polygon

NOTE: this tends to make the county polygons bigger. In the case where two counties delineate the same boundary, this will produce the correct answer. If they disagree, this will build the largest polygon for the county. This means that two counties can have overlapping boundaries!

NOTE: another way of forming polygons would be to load all the polygon (poly2) polygons for each county and dissolve (union) them together.

Usage

1. create the base dataset

CREATE TABLE county_boundary AS
  SELECT * FROM completechain
  WHERE
     statel isnull or stater isnull or countyl isnull or countyr isnull
     or  (   (statel||'-'||countyl) != (stater||'-'||countyr) );

 alter table county_boundary add primary key (module,tlid);
   
 insert into geometry_columns values ('','','county_boundary','wkb_geometry',2,1,'GEOMETRY');
     
 create index county_boundary_indx1 on county_boundary (statel,countyl);
 create index county_boundary_indx2 on county_boundary (stater,countyr);
 vacuum analyse county_boundary;

2. create the output table

create table poly_county (
	module text, 
	gen_full geometry,
	gen_1 geometry,
	gen_2 geometry,
	 gen_3 geometry) with oids;

insert into geometry_columns values ('','','poly_county','gen_full',2,1,'GEOMETRY');
insert into geometry_columns values ('','','poly_county','gen_1',2,1,'GEOMETRY');
insert into geometry_columns values ('','','poly_county','gen_2',2,1,'GEOMETRY');
insert into geometry_columns values ('','','poly_county','gen_3',2,1,'GEOMETRY');

3. run the java code
download the countries.zip and:

source go

4. check

--should all return 0
 select count(*) from poly_county where not(isvalid(gen_full));
 select count(*) from poly_county where not(isvalid(gen_1));
 select count(*) from poly_county where not(isvalid(gen_2));
 select count(*) from poly_county where not(isvalid(gen_3));

5. add names (load the county/state names files first)

alter table poly_county add column state_abrev text;
alter table poly_county add column state_name text;
alter table poly_county add column county_name text;
 

update poly_county set state_abrev =
  ( select abrev from state_translation where 
        state_translation.module = substring (poly_county.module from 4 for 2));

update poly_county set state_name =
  ( select state_translation.name from state_translation where 
        state_translation.module = substring (poly_county.module from 4 for 2));
        
update poly_county set county_name = 
  (select county_names.name from county_names where 
     county_names.module = poly_county.module);